-- This script shows two set-based solutions.
-- The first solution runs a single UPDATE statement.
SET NOCOUNT ON
USE Northgale
go
EXEC setup_discounts

DECLARE @starttime datetime2(3) = sysdatetime(),
        @afterms   smallint

CREATE TABLE #tmporders (OrderID int NOT NULL PRIMARY KEY)

INSERT #tmporders(OrderID)
   SELECT OrderID FROM dbo.OrdersToModify()

UPDATE dbo.Orders
SET    Discount    = isnull(D.Discount, 0),
       TotalAmount = OD.ProductSum * 
                     (1 - isnull(D.Discount, 0)/100) + O.Freight
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   (SELECT OrderID, SUM(Quantity * UnitPrice) AS ProductSum
        FROM   dbo.[Order Details]
        GROUP  BY OrderID) AS OD ON OD.OrderID = O.OrderID
OUTER APPLY (SELECT TOP(1) D.Discount
             FROM   dbo.Discounts D
             WHERE  D.CustomerID = C.CustomerID OR
                    D.City       = C.City       OR
                    D.Country    = C.Country    OR
                    D.CustomerID IS NULL AND 
                       D.City    IS NULL AND 
                       D.Country IS NULL
             ORDER  BY D.CustomerID DESC, 
                       D.City       DESC, 
                       D.Country    DESC) AS D               
WHERE  O.Discount <> isnull(D.Discount, 0)
  AND  EXISTS (SELECT *
               FROM   #tmporders t
               WHERE  t.OrderID = O.OrderID) 


SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('All rows processed in %d ms.', 0, 1, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders
go
--===================================================================================================
-- A different set-based solution that uses an intermediate temp table. More simple-minded SQL,
-- but as it happens, also faster
SET NOCOUNT ON

EXEC setup_discounts

DECLARE @starttime datetime2(3) = sysdatetime(),
        @afterms   smallint

CREATE TABLE #tmporders (OrderID    int      NOT NULL PRIMARY KEY,
                         CustomerID nchar(5) NOT NULL)

CREATE TABLE #customers (CustomerID nchar(5)     NOT NULL PRIMARY KEY,
                         City       nvarchar(25) NOT NULL,
                         Country    nvarchar(15) NOT NULL,
                         Discount   decimal(5,2) NULL)

INSERT #tmporders(OrderID, CustomerID)
   SELECT O.OrderID, O.CustomerID
   FROM   dbo.Orders O
   WHERE  O.OrderID IN (SELECT M.OrderID FROM dbo.OrdersToModify() M)

INSERT #customers(CustomerID, City, Country)
   SELECT C.CustomerID, C.City, C.Country
   FROM   dbo.Customers C
   WHERE  EXISTS (SELECT *
                  FROM   #tmporders t
                  WHERE  C.CustomerID = t.CustomerID)

UPDATE #customers
SET    Discount = (SELECT D.Discount
                   FROM   dbo.Discounts D
                   WHERE  c.CustomerID = D.CustomerID)
FROM   #customers c

UPDATE #customers
SET    Discount = (SELECT D.Discount
                   FROM   dbo.Discounts D 
                   WHERE  c.City = D.City)
FROM   #customers c
WHERE  c.Discount IS NULL

UPDATE #customers
SET    Discount = (SELECT D.Discount
                   FROM   dbo.Discounts D 
                   WHERE  c.Country = D.Country)
FROM   #customers c
WHERE  c.Discount IS NULL

UPDATE #customers
SET    Discount = (SELECT D.Discount
                   FROM   dbo.Discounts D
                   WHERE  D.CustomerID IS NULL
                     AND  D.City       IS NULL
                     AND  D.Country    IS NULL)
FROM   #customers c
WHERE  c.Discount IS NULL

UPDATE dbo.Orders
SET    Discount    = isnull(c.Discount, 0),
       TotalAmount = OD.ProductSum * 
                     (1 - isnull(c.Discount, 0)/100) + O.Freight
FROM   dbo.Orders O
JOIN   #customers c ON O.CustomerID = c.CustomerID
JOIN   (SELECT OrderID, SUM(Quantity * UnitPrice) AS ProductSum
        FROM   dbo.[Order Details]
        GROUP  BY OrderID) AS OD ON OD.OrderID = O.OrderID
WHERE  O.Discount <> isnull(c.Discount, 0)
  AND  EXISTS (SELECT *
               FROM   #tmporders t
               WHERE  t.OrderID = O.OrderID) 


SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('All rows processed in %d ms.', 0, 1, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders, #customers    
go
/* Drop the indexes we added earlier and run again.
DROP INDEX IF EXISTS Discounts.CustomerID_ix
DROP INDEX IF EXISTS Discounts.City_ix
DROP INDEX IF EXISTS Discounts.Country_ix
*/